import tkinter as tk
from tkinter import messagebox
import pymssql

root = tk.Tk()
root.title("植物监测指标")
root.geometry("500x300")
def add_data():
    # 获取输入框中的数据
    plantid= plantid_entry.get()
    leaf_area=leaf_area_entry.get()
    temperature = temperature_entry.get()
    humidity = humidity_entry.get()
    height = height_entry.get()

    # 将数据插入到数据库中
    conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统   ',
        as_dict=True
    )
    c = conn.cursor()
    c.execute(f"INSERT INTO 监测指标 (植物编号,叶片面积,环境温度,环境湿度,植物高度) VALUES ('{id}', '{time}','{person}','{object}','{location}','{device}')")
    conn.commit()
    conn.close()
    # 清空输入框
    plantid_entry.delete(0, tk.END)
    leaf_area_entry.delete(0, tk.END)
    temperature_entry.delete(0, tk.END)
    humidity_entry.delete(0, tk.END)
    height_entry.delete(0, tk.END)

#修改
def update_data():
    # 获取输入框中的数据
    plantid = plantid_entry.get()
    leaf_area = leaf_area_entry.get()
    temperature = temperature_entry.get()
    humidity = humidity_entry.get()
    height = height_entry.get()

    # 更新数据库中的数据
    conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统',
        as_dict=True
    )
    c = conn.cursor()
    c.execute("UPDATE 监测 SET  监测时间=%s, 监测人员=%s, 监测地点=%s, 监测对象=%s, 监测设备=%s WHERE 监测编号=%s",
              (time, person, location, object, device, id))
    conn.commit()
    conn.close()

    # 清空输入框
    plantid_entry.delete(0, tk.END)
    leaf_area_entry.delete(0, tk.END)
    temperature_entry.delete(0, tk.END)
    humidity_entry.delete(0, tk.END)
    height_entry.delete(0, tk.END)

#删除
def delete_data():
    #获取输入框中的ID
    plantid = plantid_entry.get()

    # 从数据库中删除数据
    conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统',
        as_dict=True
    )
    c = conn.cursor()
    c.execute("DELETE FROM 监测 WHERE 监测编号=%s", (id,))
    conn.commit()
    conn.close()

    # 清空输入框
    plantid_entry.delete(0, tk.END)
#查询
def query_data():
    # 获取输入框中的ID
    plantid = plantid_entry.get()

    # 从数据库中查询数据
    conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统',
        as_dict=True
    )
    c = conn.cursor()
    c.execute("SELECT * FROM 监测 WHERE 监测编号=%s", (id,))
    data = c.fetchone()

    # 显示查询结果
    if data:
        leaf_area_entry.delete(0, tk.END)
        temperature_entry.delete(0, tk.END)
        humidity_entry.delete(0, tk.END)
        height_entry.delete(0, tk.END)

        leaf_area_entry.insert(0, data['叶片面积'])
        temperature_entry.insert(0, data['环境温度'])
        humidity_entry.insert(0, data['环境湿度'])
        height_entry.insert(0, data['植物高度'])
    else:
        messagebox.showerror("错误", "未找到对应的数据")

    conn.close()
#平均值查询
def average_query():
    conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统'
    )
    c = conn.cursor()
    c.execute("SELECT AVG(叶片面积) FROM 监测指标")
    avg_leaf_area = c.fetchone()[0]
    c.execute("SELECT AVG(环境温度) FROM 监测指标")
    avg_temperature = c.fetchone()[0]
    c.execute("SELECT AVG(环境湿度) FROM 监测指标")
    avg_humidity = c.fetchone()[0]
    c.execute("SELECT AVG(植物高度) FROM 监测指标")
    avg_height = c.fetchone()[0]
    result_text.delete(1.0, tk.END)
    result_text.insert(tk.END, f"平均叶片面积:{avg_leaf_area}平均环境温度： {avg_temperature}平均环境湿度： {avg_humidity}平均植物高度： {avg_height}")
    conn.commit()
    conn.close()
#最大值查询
def max_query():
    conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统'
    )
    c = conn.cursor()
    c.execute("SELECT MAX(叶片面积) FROM 监测指标")
    max_leaf_area = c.fetchone()[0]
    c.execute("SELECT MAX(环境温度) FROM 监测指标")
    max_temperature = c.fetchone()[0]
    c.execute("SELECT MAX(环境湿度) FROM 监测指标")
    max_humidity = c.fetchone()[0]
    c.execute("SELECT MAX(植物高度) FROM 监测指标")
    max_height = c.fetchone()[0]
    result_text.delete(1.0, tk.END)
    result_text.insert(tk.END, f"最大叶片面积： {max_leaf_area}最大环境温度： {max_temperature}最大环境湿度： {max_humidity}最大植物高度： {max_height}")
    conn.commit()
    conn.close()
# 创建输入框和标签
id_label = tk.Label(root, text="植物编号")
id_label.grid(row=0, column=0)
id_entry = tk.Entry(root)
id_entry.grid(row=0, column=1)

time_label = tk.Label(root, text="叶片面积")
time_label.grid(row=1, column=0)
time_entry = tk.Entry(root)
time_entry.grid(row=1, column=1)

person_label = tk.Label(root, text="环境温度")
person_label.grid(row=2, column=0)
person_entry = tk.Entry(root)
person_entry.grid(row=2, column=1)

location_label = tk.Label(root, text="环境湿度")
location_label.grid(row=3, column=0)
location_entry = tk.Entry(root)
location_entry.grid(row=3, column=1)

object_label = tk.Label(root, text="植物高度")
object_label.grid(row=4, column=0)
object_entry = tk.Entry(root)
object_entry.grid(row=4, column=1)

# 创建按钮并绑定事件处理函数
add_button = tk.Button(root, text="添加",command=add_data)
add_button.grid(row=6, column=0)
update_button = tk.Button(root, text="修改",command=update_data)
update_button.grid(row=6, column=1)
delete_button = tk.Button(root, text="删除",command=delete_data)
delete_button.grid(row=6, column=2)
query_button = tk.Button(root, text="查询",command=query_data)
query_button.grid(row=6, column=3)
add_button = tk.Button(root, text="平均值查询",command=average_query)
add_button.grid(row=7, column=0)
update_button = tk.Button(root, text="最大值查询",command=max_query)
update_button.grid(row=7, column=1)
result_text = tk.Text(root, width=40, height=10)
result_text.grid(row=8, column=0, columnspan=2)

root.mainloop()